package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"log"
	"strconv"
	"time"
)

type Student struct {
	Id         int64   `json:"id,omitempty"`
	Name       string  `json:"name,omitempty"`
	Province   string  `json:"province,omitempty"`
	City       string  `json:"city,omitempty"`
	Addr       string  `json:"addr,omitempty"`
	Score      float64 `json:"score,omitempty"`
	Enrollment string  `json:"enrollment,omitempty"`
}

var (
	DbUser = "admin"
	DbPass = "yicai127"
	DbIp   = "192.168.0.105:3306"
	DbName = "test"
)

func ConnectDb() (*sql.DB, error) {
	DbAddr := DbUser + ":" + DbPass + "@" + "(" + DbIp + ")" + "/" + DbName + "?charset=utf8"
	db, err := sql.Open("mysql", DbAddr)
	if err != nil {
		fmt.Printf("open mysql error: %v\n", err)
		return nil, err
	}
	err = db.Ping()
	if err != nil {
		fmt.Printf("connect mysql error: %v\n", err)
		return nil, err
	}
	fmt.Println("connect success", DbIp)
	return db, nil
}

func DbQuery(db *sql.DB) {
	rows, err := db.Query("select * from student;")
	//defer db.Close()
	defer rows.Close()
	if err != nil {
		fmt.Printf("query error: %v\n", err)
		return
	}
	fmt.Println("query success")
	var stu Student
	for rows.Next() {
		e := rows.Scan(&stu.Id, &stu.Name, &stu.Province, &stu.City, &stu.Addr, &stu.Score, &stu.Enrollment)
		if e != nil {
			fmt.Printf("scan error: %v\n", e)
			return
		}
		b, _ := json.Marshal(stu)
		fmt.Println(string(b))
		time.Sleep(1 * time.Second)
	}
}

func DbInsert(db *sql.DB) {
	tx, err := db.Begin()
	if err != nil {
		log.Fatal(err)
		return
	}

	Sql := "insert into student (`name`,`province`,`city`,`addr`,`score`,`enrollment`) values (?,?,?,?,?,?)"
	stmt, err := tx.Prepare(Sql)
	defer stmt.Close()
	if err != nil {
		log.Fatal(err)
		return
	}

	stu := Student{
		Name:       "lwq",
		Province:   "Chn",
		City:       "shanghai",
		Addr:       "PuDong",
		Score:      0,
		Enrollment: "2021-09-03",
	}
	for i := 0; i < 100000; i++ {
		_, err := stmt.Exec(stu.Name+strconv.Itoa(i), stu.Province, stu.City, stu.Addr, stu.Score, stu.Enrollment)
		if err != nil {
			log.Fatal(err)
			return
		}

		//fmt.Println(res.LastInsertId())
	}
	tx.Commit()
}

func DbDelete(db *sql.DB) {
	tx, err := db.Begin()
	if err != nil {
		log.Fatal(err)
		return
	}
	Sql := "delete from student where name like 'd%';"
	stmt, err := tx.Prepare(Sql)
	defer stmt.Close()
	if err != nil {
		log.Fatal(err)
		return
	}
	res, err := stmt.Exec()
	if err != nil {
		log.Fatal(err)
		return
	}

	tx.Commit()
	fmt.Println(res.LastInsertId())

}

func Traverse(db *sql.DB) {
	var offset int
	begin := time.Now()
	stmt, _ := db.Prepare("select * from student limit ?,100")
	for i := 0; i < 100; i++ {
		t0 := time.Now()
		stmt.Query(offset)
		offset += 100
		done := time.Since(t0)
		fmt.Println(i, done)
	}
	fmt.Println("total", time.Since(begin))
}

func Traverse2(db *sql.DB) {
	var maxid int
	begin := time.Now()
	stmt, _ := db.Prepare("select * from student where id >? limit 100")
	for i := 0; i < 100; i++ {
		t0 := time.Now()
		rows, _ := stmt.Query(maxid)
		done := time.Since(t0)
		fmt.Println(i, done)

		for rows.Next() {
			var id int
			var name string
			var province string
			rows.Scan(&id, &name, &province)
			if id > maxid {
				maxid = id
			}
		}
	}
	fmt.Println("total", time.Since(begin))
}

func main() {
	db, err := ConnectDb()
	defer db.Close()
	if err != nil {
		return
	}
	//DbQuery(db)
	//DbInsert(db)
	//DbDelete(db)
	Traverse2(db)
}
